package org.shoukaiseki.commons.utils.mysql;

import org.apache.commons.lang3.StringUtils;
import org.shoukaiseki.commons.utils.sql.PrintResultSet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Optional;

/**
 * <br>
 *
 * @author 蒋カイセキ    Japan-Tokyo  18-10-23 下午7:56<br>
 * ブログ http://www.shoukaiseki.top/<br>
 * E-メール jiang28555@Gmail.com<br>
 **/
public class MysqlCommentUtils {

    Connection connection;

    String sqlTable = "SELECT\n" +
            " table_name ,\n" +
            " table_comment ,\n" +
            "       TABLE_SCHEMA\n" +
            " FROM\n" +
            " information_schema.TABLES\n" +
            " WHERE "+
            "(table_schema = ? or ? ='')\n" +
            " and table_name =?";



    String sqlColumn = "SELECT\n" +
            "       a.table_name ,\n" +
            "       b.column_name ,\n" +
            "       b.column_comment ,\n" +
            "       b.column_type ,\n" +
            "       a.table_comment ,\n" +
            "       b.column_key \n" +
            "FROM\n" +
            "     information_schema. TABLES a\n" +
            "       LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME\n" +
            "WHERE\n" +
            "    (a.table_schema = ? or ? ='')\n" +
            "  and a.TABLE_NAME = ?\n" +
            "  and b.column_name=?\n" +
            "ORDER BY\n" +
            "         a.table_name";

    PreparedStatement psTable;

    PreparedStatement psColumn;


    public MysqlCommentUtils(Connection connection) throws SQLException {
        this.connection=connection;
        init();
    }

    private void init() throws SQLException {

        psTable = connection.prepareStatement(sqlTable);
        psColumn=connection.prepareStatement(sqlColumn);
    }

    public Optional<String> getTableComment(String schemaName,String tableName) throws SQLException {

        psTable.setString(1,schemaName);
        psTable.setString(2,schemaName);
        psTable.setString(3,tableName);
//        System.out.println("sql=\n"+sqlTable);
//        System.out.println("schemaName="+schemaName);
//        System.out.println("tableName="+tableName);
        ResultSet rs = psTable.executeQuery();
        if(rs.next()){
            String table_comment = rs.getString("table_comment");
//            System.out.println("rs.next.table_comment="+table_comment);
            return Optional.of(table_comment);
        }
//        System.out.println("rs.nonext");
        return Optional.empty();
    }

    public Optional<String> getColumnComment(String schemaName,String tableName,String columnName) throws SQLException {

        psColumn.setString(1,schemaName);
        psColumn.setString(2,schemaName);
        psColumn.setString(3,tableName);
        psColumn.setString(4,columnName);
        ResultSet rs = psColumn.executeQuery();
        if(rs.next()){
//            PrintResultSet.printSingle(rs);
//            String tableComment = rs.getString("table_comment");
            String columnComment = rs.getString("column_comment");
            return Optional.of(columnComment);
        }
        return Optional.empty();
    }

    /**
     * 是否主键
     * @param schemaName
     * @param tableName
     * @param columnName
     * @return
     * @throws SQLException
     */
    public boolean  isPrimaryKey(String schemaName,String tableName,String columnName) throws SQLException {
        psColumn.setString(1,schemaName);
        psColumn.setString(2,schemaName);
        psColumn.setString(3,tableName);
        psColumn.setString(4,columnName);
        ResultSet rs = psColumn.executeQuery();
        if(rs.next()){
//            PrintResultSet.printSingle(rs);
//            String tableComment = rs.getString("table_comment");
            String column_key = rs.getString("column_key");
            return StringUtils.isNotBlank(column_key);
        }
        return false;
    }


}
